﻿Imports System.Data.OleDb

Public Class hotelDetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'The below is to create a data reader to populate Gridview 2 dynamically - Unit 4 Session 3 Part 1
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("hotelList.aspx")
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("travelConnectionString").ConnectionString)
            Dim SqlString As String = "SELECT * FROM Comments WHERE Hotel_fkID=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            oleDbConn.Open()
            Dim dataReader = cmd.ExecuteReader()
            GridView2.DataSource = dataReader
            GridView2.DataBind()

            '------------ End Data Reader

            'This part will log the username of the user who has viewed the hotel - Unit6 Session6
            'This is a typical insert statement

            Dim hotelsViewedSql As String = "INSERT INTO HotelsViewed (Traveller,Hotel_fkID) VALUES (@f1,@f2)"
            Dim cmd2 As OleDbCommand = New OleDbCommand(hotelsViewedSql, oleDbConn)
            cmd2.CommandType = CommandType.Text
            cmd2.Parameters.AddWithValue("@f1", User.Identity.Name) 'User identity will be saved in Traveller
            cmd2.Parameters.AddWithValue("@f2", Request.Params("ID"))
            cmd2.ExecuteNonQuery()

        End If
    End Sub

    Protected Sub btn_AddComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddComment.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("travelConnectionString").ConnectionString)
        Dim SqlString As String = "Insert into Comments(Author,Comment,Title,Hotel_fkID) Values (@f1,@f2,@f3,@f4)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name) 'This is so that only logged in users can comment
        cmd.Parameters.AddWithValue("@f2", tb_Comment.Text)
        cmd.Parameters.AddWithValue("@f3", tb_Title.Text)
        cmd.Parameters.AddWithValue("@f4", Request.Params("ID"))
        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        'The below is to create a to show the comment immediately in gridview 2 - Unit 4 Session 4 Part 2
        Dim fillComments As String = "SELECT * FROM Comments WHERE Hotel_fkID=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dataReader = fillCommentsCmd.ExecuteReader()
        GridView2.DataSource = dataReader
        GridView2.DataBind()

        'The following 3 lines are to clear the textboxes after the form has been submitted
        tb_Comment.Text = ""
        tb_Title.Text = ""

        'Response.Redirect("commentConfirmation.aspx")
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        'Create the database connection and fill in the 2 fields of the HotelTagJunction table i.e. Hotel_fkid and Tag_fkid
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("travelConnectionString").ConnectionString)
        Dim SqlString As String = "INSERT INTO HotelTagJunction(Hotel_fkid,Tag_fkid) Values (@f1,@f2)"
        'Open the database connection
        oleDbConn.Open()

        For Each cb As ListItem In CheckBoxList1.Items
            If cb.Selected Then
                'here is where we add the join to the table of tags and hotels
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text

                'here is where we will be going through each of the checkboxes and associate the value behind the checkbox with the value of the hotel
                'request.params is used to read the query string and know which hotel we are in right now. Since we are passing the querystring to the page as ID:
                cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

                'next we write into the foreign key ID of the Tag. We get it from the checkbox - the temp variable cb
                cmd.Parameters.AddWithValue("@f2", cb.Value)

                'execute non query
                cmd.ExecuteNonQuery()

            End If
        Next

        'If the NewTags is empty - do not add any new tags:
        If TextBox1.Text = "" Then Return

        'This will take the content of the textbox and break it into an array of words
        Dim newTags = TextBox1.Text.Split(",")

        'Go through each new tag and add it to the table of tags
        For Each newTag In newTags

            '1. Add the new tag to the tag table
            Dim newTagSql As String = "INSERT INTO Tags(Tag) VALUES(@f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            '2. Find the ID of the new tag in the tag table - 
            'The last item has the largest ID number! - SELECT TOP 1 in Table ORDERBY ID Desc
            Dim getLastIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID from Tags ORDER BY ID DESC", oleDbConn)
            'we need a value so we don't do ExecuteNonQuery
            Dim dr = getLastIdCmd.ExecuteReader()
            dr.Read()
            Dim lastId = dr.GetValue(0)

            '3. Associate this last ID with the Hotel ID - this is like the first for loop above

            'we create newcmd because cmd has already been used above
            Dim newcmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            newcmd.CommandType = CommandType.Text
            'Hotel ID is fine again since we are on its page
            newcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            'But the tag foreign key ID is the ID we have just fetched
            newcmd.Parameters.AddWithValue("@f2", lastId)
            newcmd.ExecuteNonQuery()

        Next

    End Sub

    Protected Sub btn_Rating_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Rating.Click
        'Create connection and open it
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("travelConnectionString").ConnectionString)
        oleDbConn.Open()
        'Delete previous rating feature
        Dim delRatingSql As String = "DELETE FROM HotelUserRating WHERE Hotelid = @f1 AND Reader = @f2"
        Dim delRatingCmd As New OleDbCommand(delRatingSql, oleDbConn)
        delRatingCmd.Parameters.AddWithValue("@f1", Request.Params("ID")) 'get the ID of the hotel
        delRatingCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        delRatingCmd.ExecuteNonQuery()

        'Add the new rating
        'Create SQL
        Dim addRatingSQL As String = "INSERT INTO HotelUserRating(Hotelid,Reader,Rating) VALUES(@f1,@f2,@f3)"
        'Create Command Object
        Dim addRatingCmd As New OleDbCommand(addRatingSQL, oleDbConn)
        addRatingCmd.Parameters.AddWithValue("@f1", Request.Params("ID")) 'get the ID of the hotel
        addRatingCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        addRatingCmd.Parameters.AddWithValue("@f3", Rating1.CurrentRating) 'rating 1 is the ID of the rating control
        addRatingCmd.ExecuteNonQuery()
        Label1.Text = "Thank you for adding your rating"
    End Sub
End Class